# -*- coding: UTF-8 -*-
"""
@auth:buxiangjie
@date:2022/2/11 09:31
@describe: 
"""
from common.db_operate.conn_db import ConnMysql
from common.common_func import Common
from log.ulog import logger_
from common.common_func import Encoder

import json
import allure
import pytest


class Saas(ConnMysql):
	"""
	请求参数中部分字段从数据库中动态获取
	"""

	@allure.step("获取授信ID")
	def get_credit_id(self, **kwargs) -> int:
		"""获取授信ID"""
		if kwargs.get("product_group_id", None):
			sql = f"""
				select id from credit where 
				product_group_id={kwargs["product_group_id"]}
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "user_id":
			sql = f"""
				select user_id as id from credit 
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "source_user_id":
			sql = f"""
				select source_user_id as id from credit
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select id from credit order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["id"]
		return res

	@allure.step("获取进件ID")
	def get_project_id(self, **kwargs) -> int:
		"""获取进件ID"""
		if kwargs.get("product_code", None):
			sql = f"""
				select id from project_detail where product_code='{kwargs["product_code"]}'
				order by rand() limit 1;
			"""
		elif kwargs.get("cancel", "") == "yes":
			sql = f"""
				select id from project_detail where audit_status=2 
				and audit_result=1 and loan_step=0 
				and project_step=5 and channel != "Pintec"
				and loan_result=-1
				order by rand() limit 1;
			"""
		elif kwargs.get("cancel", "") == "no":
			sql = f"""
				select id from project_detail where loan_step !=0
				and audit_result != 1
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "loan_confirm":
			sql = f"""
				select id from project_detail where
				product_code = "FQ_ZQYC_DCD"
				and audit_result=1
				and loan_step=0
				order by rand() limit 1;
			"""
			res = self.exec_select(sql, kwargs["env"])[0]["id"]
			return [res]
		elif kwargs.get("types", "") == "loan_confirm_search":
			sql = f"""
				select id from project_detail where
				product_code = "FQ_ZQYC_DCD"
				and audit_result=1
				and loan_step=0
				and apply_time between '{kwargs["startDate"]} 00:00:00'
				and '{kwargs["startDate"]} 23:25:59'
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "asset_list":
			sql = f"""
				select project_id as id from asset
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select id from project_detail order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["id"]
		return res

	@allure.step("获取资产ID")
	def get_asset_id(self, **kwargs) -> int:
		"""获取资产ID"""
		if kwargs.get("product_code", None):
			sql = f"""
				select id from asset 
				where product_code = '{kwargs["product_code"]}'
				order by rand() limit 1;
			"""
		elif kwargs.get("source_code", "") == "self":
			sql = f"""
				select id from asset where source_code > 1
				order by rand() limit 1;
			"""
		elif kwargs.get("source_code", "") == "pintec":
			sql = f"""
				select id from asset where source_code <= 1
				order by rand() limit 1;
			"""
		elif kwargs.get("method", "") == "swap":
			sql = f"""
				select asset_id as id from asset_swap_apply 
				where credit_assign_method=3 
				order by rand() limit 1;
			"""
		elif kwargs.get("method", "") == "relief":
			sql = f"""
				select asset_id as id from relief_apply
				order by rand() limit 1;
			"""
		elif str(kwargs.get("deduction_result", "")) == "1":
			sql = f"""
				select a.id from asset a, deduction_flow df 
				where a.project_id=df.association_id 
				and df.deduction_result={kwargs["deduction_result"]}
				order by rand() limit 1;
			"""
		elif str(kwargs.get("deduction_result", "")) == "0":
			sql = f"""
				select a.id from asset a, deduction_flow df 
				where a.project_id=df.association_id 
				and df.deduction_result={kwargs["deduction_result"]}
				order by rand() limit 1;
			"""
		elif kwargs.get("repay_type", None):
			sql = f"""
				select asset_id as id from repayment where
				repay_type = {kwargs["repay_type"]}
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "confirm_contract":
			sql = f"""
				select asset_id as id from business_confirm_record
				where business_date = '{kwargs["businessDate"]}'
				and product_group_id={kwargs["productGroupId"]}
				and business_event_type={kwargs["businessEventType"]}
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") in ("relief_check", "relief_confirm"):
			sql = f"""
				select id from asset where source_code != 1 
				and source_code != 0
				and product_code not in('FQ_JK_CKSHD', 'FQ_JK_CWSHD')
				and asset_status = 1
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "repay_confirm":
			sql = f"""
				select id from asset where product_code='FQ_ZQYC_DCD'
				and asset_status = 1
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "settle_confirm":
			sql = f"""
				select id from asset where product_code='FQ_ZQYC_DCD'
				and asset_status = 1 and first_repayment_date>CURRENT_DATE
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "return_confirm":
			sql = f"""
				select id from asset where product_code='FQ_ZQYC_DCD'
				and asset_status = 1
				and timestampdiff(DAY , loan_time, curtime()) < 4
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select id from asset order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["id"]
		return res

	@allure.step("获取额度ID")
	def get_amount_id(self, **kwargs) -> int:
		"""获取额度ID"""
		if kwargs.get("occurrence", None):
			sql = f"""
				select amount_id as id from amount_detail
				where occurrence={kwargs["occurrence"]}
				order by rand() limit 1;
			"""
		elif kwargs.get("group_id", None):
			sql = f"""
				select id from amount
				where product_group_id={kwargs['group_id']}
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select amount_id as id from amount_detail
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"], "nebula")[0]["id"]
		return res

	@allure.step("获取额度明细ID")
	def get_amount_detail_id(self, **kwargs) -> int:
		"""获取额度ID"""
		if kwargs.get("need_amount_id", None):
			sql = f"""
				select id from amount_detail
				where occurrence={kwargs["occurrence"]}
				and amount_id={kwargs["amountId"]}
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select id from amount_detail
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"], "nebula")[0]["id"]
		return res

	@allure.step("获取合同确认相关")
	def get_contract_confirm_id(self, **kwargs) -> list:
		"""获取合同确认相关"""
		if kwargs.get("business_event_type", None):
			sql = f"""
				select id from business_confirm_statistic
				where confirm_status != 2
				and business_event_type={kwargs["business_event_type"]}
				and business_date >= '2022-01-01'
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select id from business_confirm_statistic
				where confirm_status != 2
				and business_date >= '2022-01-01'
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])
		if res is None:
			pytest.skip("无数据")
		else:
			ret = res[0]["id"]
			return [ret]

	@allure.step("获取合同确认详情相关")
	def get_contract_confirm_record_id(self, **kwargs) -> list:
		"""获取合同确认详情相关"""
		sql = f"""
			select id from business_confirm_record
			where confirm_status=0
			and business_date >= '2022-01-01'
			order by rand() limit 1;
		"""
		try:
			res = self.exec_select(sql, kwargs["env"])[0]["id"]
			return [res]
		except TypeError:
			pytest.skip("无数据")

	@allure.step("获取债转合同确认业务时间")
	def get_contract_confirm_business_date(self, **kwargs) -> str:
		"""获取债转合同确认业务时间"""
		if kwargs.get("types", "") == "detail":
			sql = f"""
				select business_date from business_confirm_statistic
				where channel='wxjk'
				and business_event_type=80
				and business_date >= '2022-01-01'
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "record":
			sql = f"""
				select business_date from business_confirm_statistic
				where product_group_id={kwargs["productGroupId"]} 
				and business_event_type={kwargs["businessEventType"]}
				and confirm_status !=2
				and business_date >= '2022-01-01'
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "record_channel":
			sql = f"""
				select business_date from business_confirm_statistic
				where product_group_id={kwargs["productGroupId"]} 
				and business_event_type={kwargs["businessEventType"]}
				and confirm_status != 2
				and business_date >= '2022-01-01'
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select business_date from business_confirm_statistic
				where confirm_status !=2
				and business_date >= '2022-01-01'
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])
		if res is None:
			pytest.skip("无业务数据")
		res = res[0]["business_date"]
		return res

	@allure.step("获取来源进件ID")
	def get_source_project_id(self, **kwargs) -> str:
		"""获取来源进件ID"""
		if kwargs.get("types", "") == "confirm_contract":
			sql = f"""
				select source_project_id as id from business_confirm_record
				where business_date = '{kwargs["businessDate"]}'
				and product_group_id={kwargs["productGroupId"]}
				and business_event_type={kwargs["businessEventType"]}
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "loan_confirm":
			sql = f"""
				select source_project_id as id from project_detail
				where apply_time between '{kwargs["startDate"]} 00:00:00'
				and '{kwargs["startDate"]} 23:25:59'
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "asset_list":
			sql = f"""
				select source_project_id as id from asset
				order by rand() limit 1;
			"""
		else:
			sql = f"""
				select source_project_id as id from project_detail
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["id"]
		return res

	@allure.step("获取进件申请时间")
	def get_apply_time(self, **kwargs) -> str:
		"""获取进件申请时间"""
		if kwargs.get("types", "") == "loan_confirm":
			sql = f"""
				select apply_time from project_detail
				where id='{kwargs["projectIdList"][0]}';
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["apply_time"]
		return str(res).split(" ")[0]

	@allure.step("获取用户ID")
	def get_user_id(self, **kwargs) -> str:
		"""获取用户ID"""
		if kwargs.get("types", "") == "project_list":
			sql = f"""
				select user_id from project_detail
				where user_id !=0
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "asset_list":
			sql = f"""
				select pd.user_id from project_detail pd,
				asset a where pd.id=a.project_id
				and pd.user_id !=0
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "amount_list":
			sql = f"""
				select user_id from amount
				order by rand() limit 1;
			"""
			res = self.exec_select(sql, kwargs["env"], "nebula")[0]["user_id"]
			return res
		res = self.exec_select(sql, kwargs["env"])[0]["user_id"]
		return res

	@allure.step("获取来源用户ID")
	def get_source_user_id(self, **kwargs) -> str:
		"""获取来源用户ID"""
		if kwargs.get("types", "") == "project_list":
			sql = f"""
				select source_user_id from project_detail
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "asset_list":
			sql = f"""
				select pd.source_user_id from project_detail pd,
				asset a where pd.id=a.project_id
				and pd.user_id !=0
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "amount_list":
			sql = f"""
				select source_user_id from amount
				order by rand() limit 1;
			"""
			res = self.exec_select(sql, kwargs["env"], "nebula")[0]["source_user_id"]
			return res
		res = self.exec_select(sql, kwargs["env"])[0]["source_user_id"]
		return res

	@allure.step("获取身份证号")
	def get_card_num(self, **kwargs) -> str:
		"""获取身份证号"""
		if kwargs.get("types", "") == "person":
			sql = f"""
				select pcd.card_num_encrypt as card_num
				from project_customer_detail pcd, project_detail pd
				where pcd.project_id=pd.id
				and channel='wxjk'
				order by rand() limit 1;
			"""
		elif kwargs.get("types", "") == "company":
			sql = f"""
				select pcd.card_num_encrypt as card_num
				from project_customer_detail pcd, project_detail pd
				where pcd.project_id=pd.id
				and channel='zqyc'
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["card_num"]
		return Common.info_crypt(kwargs["env"], res, False)

	@allure.step("获取场景方ID")
	def get_transaction_id(self, **kwargs) -> str:
		"""获取场景方ID"""
		if kwargs.get("types", "") == "asset_list":
			sql = f"""
				select transaction_id as id from asset
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["id"]
		return res

	def get_repayment_plan_data(self, **kwargs) -> dict:
		get_table = self.get_sub_table(kwargs["env"], kwargs["assetId"])
		sql = f"""
			select * from repayment_plan_0{get_table}
			where asset_id='{kwargs["assetId"]}'
			and repayment_status != 9
			and repayment_plan_type={kwargs["planCategory"]}
			and period={kwargs["period"]};
		"""
		res = self.exec_select(sql, kwargs["env"])[0]
		return res

	def get_fee_plan_data(self, **kwargs) -> dict:
		get_table = self.get_sub_table(kwargs["env"], kwargs["assetId"])
		sql = f"""
			select * from fee_plan_0{get_table}
			where asset_id='{kwargs["assetId"]}'
			and fee_category={kwargs["planCategory"]}
			and fee_status != 9
			and period={kwargs["period"]};
		"""
		res = self.exec_select(sql, kwargs["env"])[0]
		return res

	@allure.step("获取日志ID")
	def get_log_id(self, **kwargs) -> str:
		"""获取日志ID"""
		sql = f"""
			select id from operation_log
			order by rand() limit 1;
		"""
		res = self.exec_select(sql, kwargs["env"], "mart")[0]["id"]
		return res

	@allure.step("获取资金流水详情")
	def get_cash_detail(self, **kwargs) -> str:
		"""获取资金流水详情"""
		if kwargs["types"] == "service_sn":
			sql = f"""
				select service_sn as id from cash_detail
				order by rand() limit 1;
			"""
		elif kwargs["types"] == "project_id":
			sql = f"""
				select project_id as id from cash_detail
				order by rand() limit 1;
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["id"]
		return res


class AssertData(ConnMysql):
	"""
	断言处理中部分参数从数据库中获取
	"""

	@allure.step("获取当前资产归属渠道")
	def get_current_channel(self, **kwargs) -> int:
		"""获取当前资产归属渠道"""
		if kwargs.get("project_id", ""):
			sql = f"""
				select current_channel from asset
				where project_id = '{kwargs["project_id"]}';
			"""
		elif kwargs.get("asset_id", ""):
			sql = f"""
				select current_channel from asset
				where id = '{kwargs["project_id"]}';
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["current_channel"]
		return res

	@allure.step("获取进件身份证号")
	def get_id_card(self, **kwargs) -> str:
		"""获取进件身份证号"""
		if kwargs.get("project_id", ""):
			sql = f"""
				select card_num_encrypt from project_customer_detail
				where project_id='{kwargs["project_id"]}';
			"""
			encrypt_card = self.exec_select(sql, kwargs["env"])[0]["card_num_encrypt"]
			decrypt_card = Common.info_crypt(kwargs["env"], encrypt_card, False)
		else:
			raise ValueError("未查询到进件ID参数")
		return decrypt_card

	@allure.step("获取资产状态")
	def get_asset_status(self, **kwargs) -> int:
		"""获取资产状态"""
		if kwargs.get("project_id", ""):
			sql = f"""
				select asset_status from asset
				where project_id='{kwargs["project_id"]}';
			"""
		else:
			sql = f"""
				select asset_status from asset
				where id='{kwargs["asset_id"]}'
			"""
		res = self.exec_select(sql, kwargs["env"])[0]["asset_status"]
		return res
